package com.api.gbhg.lbj.liubeijia.util;




import com.alibaba.fastjson.JSONObject;
import com.api.gbhg.luoao.la.dao.BudgetModifyDao;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;

import java.util.*;

public class VersionFlowUtil2 {
    private static final Logger logger = LoggerFactory.getLogger("gbhg");
    private static  String versionTableName = "uf_jingfei_version";//经费版本主表


    /**
     * 同步流程数据到版本表
     * @param requestId 请求id
     * @param workFlowId 流程配置id (对应workflow_base表中id字段)
     * @param tableEnum 流程对应枚举
     * @param specialMapping 流程抽象表与流程表的字段映射关系
     */
    public static  boolean sysDataToVersion2(String requestId, String workFlowId, GBHGConstant2.FlowVersionTableEnum2 tableEnum, Map<String, String> specialMapping,String thisVersion) {
        logger.info("进入版本备份表同步sysDataToVersion2----");
        logger.info("同步的备份表名----"+tableEnum.getFlowVersionTableName());
        int mainTableId = getFlowTableLableId(workFlowId);
        //根据流程配置id获取对应的表名
        String tableName = getFlowTableName(workFlowId);

        RecordSetTrans flowData = new RecordSetTrans();
        RecordSetTrans deldata = new RecordSetTrans();
        try {
            flowData.executeQuery("select * from " + tableName + " where requestId=" + requestId);
            flowData.next();
            //调整项目
            String dzxm = flowData.getString("dzxm");
            //调整类型
            String dzlx = flowData.getString("dzlx");
            Vector vector = flowData.getArray();
            Object[] valus = (Object[]) Arrays.stream(vector.toArray()).findFirst().orElse(null);
            String[] colums = flowData.getColumnName();

            //updateMainVersion(flowData,requestId,workFlowId, valus,colums, specialMapping,tableEnum);
            //更新流程表数据
            updateFlowVersion(flowData,requestId,tableEnum.getFlowVersionTableName(),valus,colums);
            logger.info("----主表同步完成----");
            int flowVesionTableId = getFlowTableIdByName(tableEnum.getFlowVersionTableName());

            String bidSQL="";
            int versionId=1;
            if (StringUtils.isNotEmpty(dzxm)){
                 //获取版本id
                 versionId = getVersionId(dzxm, tableEnum.getFlowVersionTableName());
                 bidSQL="select bid,requestid from "+tableEnum.getFlowVersionTableName()+" where requestId="+dzxm;
            }else {
                 bidSQL="select bid,requestid from "+tableEnum.getFlowVersionTableName()+" where requestId="+requestId;
            }
            flowData.execute(bidSQL);
            flowData.next();
            //int mainId= flowData.getInt("bid");
            int mainId= flowData.getInt("requestid");//改为用requestidid作为主键

            List<String> flowDetailTableNames=new ArrayList<>();
            String where="";
            //更新明细表到版本流程明细表
            //预算调整合并明细4金额
            /*
            if (StringUtils.isNotEmpty(dzlx)){
                //去除明细表4
                where=" and d.orderid<>4";
                flowDetailTableNames = getDetailTables(mainTableId, where);
                //获取合并后的明细表四
                String tbDetailName4=tableEnum.getFlowVersionTableName()+"_dt4";
                Map<String, Object> departmentBudgetadjustment = BudgetModifyDao.getDepartmentBudgetadjustment(null, requestId, dzxm, "2");
                List<Map<String,Object>> xfjjfmList = (List<Map<String,Object>>) departmentBudgetadjustment.get("xfjjfmList");
                logger.info("获得合并后的经济分类明细数据--"+xfjjfmList);
                //huangwm
                if(!dzlx.equals("1")){
                    flowData.execute("delete from "+tbDetailName4+" where mainid="+mainId+" and version>"+thisVersion);
                }else{
                    flowData.execute("delete from "+tbDetailName4+" where mainid="+mainId+" and version="+versionId);
                }
                for (Map<String, Object> map : xfjjfmList) {
                    String insertSQL="insert into "+tbDetailName4+" (bmyszcjjfl,hjwy,dyzfjjfl,mainid,version)values("+map.get("bmyszcjjfl")+","+map.get("hjwy")+","+map.get("dyzfjjfl")+","+mainId+","+versionId+")";
                    logger.info("同步明细表4sql----"+insertSQL);
                    flowData.executeUpdate(insertSQL);
                }
            }else {
                flowDetailTableNames = getDetailTables(mainTableId,where);
            }
            */
            if (StringUtils.isNotEmpty(dzlx)){

                //huangwm 预算新增的时候细化经济分类走直接获取插入的逻辑
                if(!dzlx.equals("1")){
                    //去除明细表4
                    where=" and d.orderid<>4";
                    flowDetailTableNames = getDetailTables(mainTableId, where);
                    //获取合并后的明细表四
                    String tbDetailName4=tableEnum.getFlowVersionTableName()+"_dt4";
                    Map<String, Object> departmentBudgetadjustment = BudgetModifyDao.getDepartmentBudgetadjustment(null, requestId, dzxm, "2");
//                    if(!dzlx.equals("1")){
//                        departmentBudgetadjustment = BudgetModifyDao.getDepartmentBudgetadjustment(null, requestId, dzxm, "2");
//                    }else{
//                        departmentBudgetadjustment = BudgetModifyDao.getDepartmentBudgetadjustment(null, requestId, requestId, "2");
//                    }
                    List<Map<String,Object>> xfjjfmList = (List<Map<String,Object>>) departmentBudgetadjustment.get("xfjjfmList");
                    //System.err.println(departmentBudgetadjustment);
                    //System.err.println(JSONObject.toJSONString(xfjjfmList));
                    logger.info("获得合并后的经济分类明细数据--"+xfjjfmList);
                    flowData.execute("delete from "+tbDetailName4+" where mainid="+mainId+" and version>"+thisVersion);
                    for (Map<String, Object> map : xfjjfmList) {
                        String insertSQL="insert into "+tbDetailName4+" (bmyszcjjfl,hjwy,dyzfjjfl,mainid,version)values("+map.get("bmyszcjjfl")+","+map.get("hjwy")+","+map.get("dyzfjjfl")+","+mainId+","+versionId+")";
                        //System.err.println("同步明细表4sql----"+insertSQL);
                        flowData.executeUpdate(insertSQL);
                    }
                }else{
                    //flowData.execute("delete from "+tbDetailName4+" where mainid="+mainId+" and version="+versionId);
                    flowDetailTableNames = getDetailTables(mainTableId,where);
                }
            }
            for(String tbname:flowDetailTableNames){
                String tbDetailName=tableEnum.getFlowVersionTableName()+"_dt"+tbname.substring(tbname.length()-1);
                //检查表是否存在
                flowData.execute("select OBJECT_ID(N'"+tbDetailName+"',N'U')");
                String tableRes= ((Object[])flowData.getArray().get(0))[0]+"";
                if(tableRes==null||tableRes.isEmpty()){
                    continue;
                }
                //huangwm
                if(!dzlx.equals("1")){
                    flowData.execute("delete from "+tbDetailName+" where mainid="+mainId+" and version>"+thisVersion);
                }else{
                    flowData.execute("delete from "+tbDetailName+" where mainid="+mainId+" and version="+versionId);
                }

                List<String> tbDetailNameFields=getTableFileds(flowVesionTableId,tbDetailName);
                String insertField=joinStringBySplit(tbDetailNameFields,",");

                System.err.println("同步明细表"+tbDetailName+"---版本号:"+versionId);
//                String insertSQL="insert into "+tbDetailName +" (version,mainid,"+insertField+") select "+versionId+" as version,"+mainId+" as mainId,"+insertField+" from "+tbname +" where mainid="+valus[0];
//                logger.info("明细表同步SQL-----"+insertSQL);
                flowData.execute("insert into "+tbDetailName +" (version,mainid,"+insertField+") select "+versionId+" as version,"+mainId+" as mainId,"+insertField+" from "+tbname +" where mainid="+valus[0]);
            }
            
            return true;
        }catch (Exception ex){
            ex.printStackTrace();
            flowData.rollback();
        }
        return false;
    }
    //获取版本id
    public static int getVersionId(String dzxm,String tableName){
        RecordSet rs=new RecordSet();
        String sql="select version from " + tableName + " where requestId=" + dzxm+" order by version desc";
        logger.info("获取版本号sql--,"+sql);
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("version");
    }



    /**
     *更新流程版本表
     * @param requestId  请求记录id
     * @param flowVesionTableName 流程表表名
     * @param valus 当前流程主表值列表
     * @param colums 当前流程表字段列表
     * @return
     */
    public static  boolean updateFlowVersion(RecordSetTrans flowData ,String requestId, String flowVesionTableName,Object[] valus ,String[] colums) throws Exception {
        RecordSet rs = new RecordSet();
        int tableId = getFlowTableIdByName(flowVesionTableName);
        String id="";
        //调整项目
        String dzxm = flowData.getString("dzxm");
        //版本号
        int version=1;
        //调整项目为空=追加或者编制流程
        if (StringUtils.isEmpty(dzxm)){
            //获取调整流程id主键
            //id = flowData.getString("id");
            id = flowData.getString("requestid");
        }else {
            flowData.execute("select bid,dzxm from "+flowVesionTableName+" where requestId="+dzxm);
            flowData.next();
            //id= flowData.getString("bid");
            id= flowData.getString("dzxm");
            //获取版本号
            version = getVersionId(dzxm, flowVesionTableName);
            logger.info("版本号:"+version);
            rs.execute("delete from "+flowVesionTableName+" where requestid="+dzxm+" and version>"+version);
            version++;
        }

        logger.info("调整流程主键id:"+id);
        List<String> tableFields = getTableFileds(tableId);
        tableFields.add(0,"requestid");

        List<String> tableFields2 = new ArrayList<>();
        tableFields2.addAll(tableFields);
        tableFields2.add("bid");
        tableFields2.add("version");

        logger.info("列数目:"+tableFields.size());
        logger.info("列数目2:"+tableFields2.size());

        flowData.executeQuery("select * from " + flowVesionTableName + " where requestid=" + requestId);
        String sql = null;
        if (!flowData.next()) {//判断是否有记录
            //首次申报
            sql = getInsertSql(tableFields2, flowVesionTableName);
        } else {
            //更新申报
            sql = getUpdateSql(tableFields2, flowVesionTableName," where requestId=" + requestId);
        }

        logger.info("预算同步编制流程到复制流程表sql---------"+sql);
        List<Object> dataArray = getDataArray(tableFields, valus, colums, null);

        logger.info("dataArray转换前---"+dataArray);

        //调整项目！=空
        if (StringUtils.isNotEmpty(dzxm)){
            //requestid替换为调整项目
            dataArray.set(0,dzxm);
        }
        //插入bid
        dataArray.add(id);
        //插入版本号
        dataArray.add(version);
        logger.info("dataArray插入bid---"+ dataArray);
        logger.info("值数目:"+dataArray.size());
        Object[] values = dataArray.toArray();//流程数据值数组

        return flowData.executeUpdate(sql, values);
    }


    /**
     * 为表字段数组取值
     * @param tableFields 表字段列表
     * @param valus 值列表
     * @param colums 列列表
     * @param specialMapping 字段映射关系map
     * @return
     */
    public static  List<Object> getDataArray(List<String> tableFields, Object[] valus, String[] colums, Map<String, String> specialMapping) {
        List<Object> obj = new ArrayList<>();
        for (String c : tableFields) {
            String cmName = c;
            if (specialMapping!=null&& specialMapping.containsKey(c)) {
                cmName = specialMapping.get(c);
            }
            obj.add(getObjValueByIndex(valus, colums, cmName));
        }
        return obj;
    }

    /**
     * 根据列的位置获取对应值，未找到的返回null
     * @param valus 值列表
     * @param colums 列列表
     * @param key 需要取值的列名
     * @return
     */
    public static  Object getObjValueByIndex(Object[] valus, String[] colums, String key) {
        for (int i = 0; i < colums.length; i++) {
            if (colums[i].toLowerCase().equals(key.toLowerCase())) {
                if(valus[i] instanceof String &&((String) valus[i]).isEmpty()){
                    return null;
                }
                return valus[i];
            }
        }
        return null;
    }

    /**
     * 根据表名，表字段生成sql插入语句
     * @param tableFields
     * @param tableName
     * @return
     */
    public static  String getInsertSql(List<String> tableFields, String tableName) {
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("insert into " + tableName + " (");
        stringBuffer.append(joinStringBySplit(tableFields, ","));
        stringBuffer.append(") values (");
        stringBuffer.append(getQuestionMark(tableFields.size()));
        stringBuffer.append(")");
        return stringBuffer.toString();
    }

    /**
     * 根据表明，表字段生成updatesql
     * @param tableFields
     * @param tableName
     * @return
     */
    public static  String getUpdateSql(List<String> tableFields, String tableName,String where) {
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("update " + tableName + " set ");
        stringBuffer.append(joinStringBySplit(tableFields, "=?,"));
        stringBuffer.append("=?");
        stringBuffer.append(where);
        return stringBuffer.toString();
    }

    /**
     * 根据指定分隔符拼接字符串
     * @param strings 字符串集合
     * @param split 分隔符
     * @return
     */
    public static  String joinStringBySplit(List<String> strings, String split) {
        return strings.stream().reduce((x, y) -> x + split + y).orElse(null);
    }

    /**
     * 生成指定数量的占位符
     * @param cnt 数目
     * @return
     */
    public static  String getQuestionMark(int cnt) {
        StringBuilder stringBuffer = new StringBuilder();
        for (int i = 0; i < cnt; i++) {
            if (i == 0) {
                stringBuffer.append("?");
            } else {
                stringBuffer.append(",?");
            }
        }
        return stringBuffer.toString();
    }


    /**
     * 根据流程配置id，获取流程表id
     *
     * @param workFlowId 流程配置id
     * @return
     */
    public static  int getFlowTableLableId(String workFlowId) {
        RecordSet rs = new RecordSet();
        rs.execute("select w.formid from workflow_base w where w.id=" + workFlowId);
        rs.next();
        return rs.getInt("formid");
    }

    /**
     * 根据流程配置id，获取流程表名
     *
     * @param workFlowId 流程配置id
     * @return
     */
    public static  String getFlowTableName(String workFlowId) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select b.tablename from workflow_bill b where b.id=(select w.formid from workflow_base w where w.id=" + workFlowId + ")");
        rs.next();
        return rs.getString("tablename");
    }

    /**
     * 根据流程主表id获取流程详细表名称列表
     *
     * @param maindId
     * @return
     */
    public static  List<String> getDetailTables(int maindId,String where) {
        List<String> resList = new ArrayList<>();
        RecordSet rs = new RecordSet();
        rs.executeQuery("SELECT d.tablename FROM workflow_billdetailtable d where d.billid=" + maindId +" "+where+" ");
        while (rs.next()) {
            resList.add(rs.getString("tablename"));
        }
        return resList;
    }


    /**
     * 根据表明查询配置表的id
     *
     * @param tableName
     * @return
     */
    public static  int getFlowTableIdByName(String tableName) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select id from workflow_bill w where w.tablename='" + tableName + "';");
        rs.next();
        return rs.getInt("id");
    }


    /**
     * 根据表id获取表字段
     * 去除bid
     * @param tableId
     * @return
     */
    public static  List<String> getTableFileds(int tableId) {
        List<String> resList = new ArrayList<>();
        RecordSet rs = new RecordSet();
        rs.executeQuery("SELECT f.fieldname FROM workflow_billfield f where f.viewtype=0 and  f.billid=" + tableId + " and f.fieldname<>'bid' and f.fieldname<>'version';");
        while (rs.next()) {
            resList.add(rs.getString("fieldname"));
        }
        return resList;
    }

    /**
     * 根据表id获取明细表字段
     *
     * @param tableId
     * @return
     */
    public static  List<String> getTableFileds(int tableId,String detailTableName) {
        List<String> resList = new ArrayList<>();
        RecordSet rs = new RecordSet();
        rs.executeQuery("SELECT f.fieldname FROM workflow_billfield f where f.detailtable='"+detailTableName+"' and  f.billid=" + tableId + " and f.fieldname<>'version';");
        while (rs.next()) {
            resList.add(rs.getString("fieldname"));
        }
        return resList;
    }


    /**
     * 根据流程记录id查询流程当前节点名称(currentnodetype 0创建，1审批，2提交，3归档)
     * @param requestId
     * @return
     */
    public static  String[] getFlowNodeName(String requestId){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select n.nodename,r.currentnodetype from workflow_requestbase r\n" +
                "left join workflow_nodebase  n on r.currentnodeid=n.id\n" +
                "where r.requestid="+requestId+";");
        rs.next();
        String nodeName= rs.getString("nodename");
        String currentnodetype=rs.getString("currentnodetype");
        return new String[]{currentnodetype,nodeName};
    }
}
